﻿#coding=utf-8

import os, sys
import MySQLdb
import myLogger
import kit
from ConfTool27 import conf

class CRunSql:

    def __init__(self):
        try:
            cls.m_conn = MySQLdb.connect(host='127.0.0.1', user='root',passwd='admin',
                            db='myshop', charset='utf8')
        except MySQLdb.Error as e:
            kit.showExcept(e)
            sys.exit()
        
        cls.m_cursor = cls.conn.cursor()
    
        # beginTrans 后取消自动提交，直到 commitTrans/rollbackTrans
        self.autoCommit = True
        
    @classmethod
    def factory(cls):
        return cls()
        
    def close(self):
        self.m_cursor.close()
        self.m_conn.close()
    
    def runSQL(self, sql, param=None):
        cursor = self.conn.cursor()
        self.m_cursor.execute(sql, param)
    
    def beginTrans(self):
        self.m_conn.begin()
        self.autoCommit = False
        return self.m_conn
       
    def commitTrans(self):
        self.m_conn.commit()
        self.autoCommit = True
        
    def rollbackTrans(self):
        self.m_conn.rollback()
        self.autoCommit = True

server_ip = '127.0.0.1'
b_printer = 0
        
class Dbmngr:

    def __init__(self):
        '''初始化数据库，如果不存在数据库，创建之'''
        
        global server_ip
        global b_printer
        server_ip = conf.get_or_default('server', 'host', '127.0.0.1')
        port = conf.getint('server', 'port', 3306)
        database = conf.get_or_default('server', 'database', 'myshop')
        username = conf.get_or_default('server', 'username', 'root')
        password = conf.get_or_default('server', 'password', 'admin')
        b_printer = conf.getint('basic', 'printer', 0)
        
        print server_ip
        #建立和数据库系统的连接
        try:
            self.conn = MySQLdb.connect(host=server_ip, user=username,passwd=password,
                        db=database, charset='utf8', port=port)
                
            #获取操作游标  
            self.cursor = self.conn.cursor()
            
            #self.conn.set_character_set('utf8')
            
            self.cursor.execute('SET NAMES utf8;') 
            self.cursor.execute('SET CHARACTER SET utf8;')
            self.cursor.execute('SET character_set_connection=utf8;')

            #执行SQL,创建一个数据库.  
            #i = self.cursor.execute("""create database if not exists myshop character set utf8""")  
           
            #选择数据库  
            #self.conn.select_db('myshop')        
            
            # beginTrans 后取消自动提交，直到 commitTrans/rollbackTrans
            self.autoCommit = True
        
        except Exception,e:
            kit.showExcept(e)
            #sys.exit()
        
    def beginTrans(self):
        self.conn.begin()
        self.autoCommit = False
        return self.conn
       
    def commitTrans(self):
        self.conn.commit()
        self.autoCommit = True
        
    def rollbackTrans(self, e=None):
        self.conn.rollback()
        self.autoCommit = True
        
        if e:
            info = u'回滚数据库操作，数据库中数据不应该被删除，如果出现显示问题，可以尝试重启。\n'\
                    u'如果存在影响使用的设计缺陷，请联系开发人员'
            kit.showExcept(e, info)
            print(u'rollback trans')
        
    def __del__(self):
        '''关闭连接，释放资源'''
        self.cursor.close()
        self.conn.close()
    
    def execute(self, sql, param=None):
        '''执行命令，返回值为受影响的行数'''

        if (param is None):
            #myLogger.logger.info(sql)
            logStr = sql
        else:
            if(type(param) is list):         
                logStr = sql %tuple(param)
            else:
                logStr = sql %param
            
            #myLogger.logger.info(logStr)
            #log.logger.info(sql, tuple(param))
            
        try:
            #print sql
            cnt = self.cursor.execute(sql, param)
            if self.autoCommit:
                #if 'select' not in sql:
                if not sql.startswith('select') and not sql.startswith('SELECT'):
                    print ('autoCommit', sql)
                self.conn.commit()
        except MySQLdb.Error,e:
            print "Mysql Error %d: %s" % (e.args[0], e.args[1])
            print (sql, param)
            kit.showExcept(e)
            myLogger.logger.exception("Mysql Error")
            myLogger.logger.exception(logStr)
            # Lost connection to MySQL server during query
            if (e.args[0] == 2013):
                self.__init__()
            # 2006 Mysql Server Has Gone Away
            if (e.args[0] == 2006):
                self.__init__()
            
            #execute()出现异常，cnt不会被赋值
            #return -1
            # 继续抛出异常，不能停止
            raise e

        #myLogger.logger.info('SQL Res:%d', cnt)
        return cnt

    def queryValue(self, sql, def_val=None):
        self.execute(sql)
        result = self.cursor.fetchone()
        return result[0] if result is not None else def_val
            
dbmngr = Dbmngr()
        
def db_init():
    pass
    
def db_tblInit(sql):
    dbmngr.execute(sql)

       
# fakeNum = 0
# def db_getSONum(date):
    # print 'SONum faked.'
    # global fakeNum
    # fakeNum = fakeNum + 1
    # return fakeNum

# def db_test():
    # '''初始化数据库，如果不存在数据库，创建之'''

    # #建立和数据库系统的连接
    # try:    
        # conn = MySQLdb.connect(host='localhost', user='root',passwd='admin')
    # except Exception,e:
        # print e
        # sys.exit()    
    # #获取操作游标  
    # cursor = conn.cursor()
    
    # #执行SQL,创建一个数据库.  
    # cursor.execute("""create database if not exists myshop character set utf8""")  
    
    # #选择数据库  
    # conn.select_db('myshop')
    
    # #执行SQL,创建一个数据表.  
    # cursor.execute("""create table if not exists test(id int, info varchar(100)) """)
    # value = [1,"inserted ?"];  
  
    # #插入一条记录  
    # cursor.execute("insert into test values(%s,%s)",value);  
      
    # values=[]  
      
      
    # #生成插入参数值  
    # for i in range(20):  
        # values.append((i,'Hello mysqldb, I am recoder ' + str(i)))  
    # #插入多条记录  
      
    # cursor.executemany("""insert into test values(%s,%s) """,values); 
    
    # #保存到数据库(持久化)
    # conn.commit()
    # print u"获取所有结果:"  
    # #重置游标位置，0,为偏移量，mode＝absolute | relative,默认为relative,  
    # #cursor.scroll(0,mode='absolute')
    # sql = "select * from test"
    # cursor.execute(sql)
    # #获取所有结果  
    # results = cursor.fetchall()  
    # for r in results:  
        # print r  
    
    # #关闭连接，释放资源  
    # cursor.close()
    # conn.close()

# db_test()

# try:
    # conn = MySQLdb.connect(host='localhost', user='root', passwd='admin', db='django',charset='utf8')
# except Exception,e:
    # print e
    # sys.exit()

# cursor = conn.cursor()
# cursor.execute('create table if not exists addressdb(name text, address text) character set utf8')


# sql = "insert into addressdb(name, address) values(% s, % s)"
# values = (('张三','北京'), ('李四','北京'))
# try:
    # cursor.executemany(sql, values)
# except Exception,e:
    # print e
# sql = "select * from addressdb"
# cursor.execute(sql)
# data = cursor.fetchall()
# if data:
    # for x in data:
        # print x[0],x[1]
# cursor.close()
# conn.close()

